﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Slja.DBUtility;
using Slja.Model;
using Slja.DAL.Common;
using System.Collections.Generic;
using Slja.Utility;

namespace Slja.DAL
{
    /// <summary>
    /// 数据访问类:OrderDAL
    /// </summary>
    public partial class OrderDAL
    {
        public OrderDAL()
        { }
        public string GetMaxPrjID(DateTime dtm,string ProductType)
        {
            string MaxPrjID = "";
            SqlParameter[] parms = new SqlParameter[2];
            parms[0] = new SqlParameter("@date", SqlDbType.DateTime);
            parms[1] = new SqlParameter("@ProductType", SqlDbType.NVarChar, 10);
            parms[0].Value = dtm;
            parms[1].Value = ProductType;
            SqlDataReader rd = DbHelperSQL.RunProcedure("biz_GetMaxPrjID", parms);
            if (rd.Read() && rd["maxPrjID"] !=null&& rd["maxPrjID"] != DBNull.Value)
            {
               MaxPrjID = rd["maxPrjID"].ToString().Trim();
            }
            return MaxPrjID;
        }
        public void SubmitWithLog(OrderSubmitModel submitModel,OrderLogModel logModel)
        {           
            if (submitModel == null || logModel == null)
            {
                throw new ArgumentNullException("对象不能为null");
            }
            KeyValuePair<string, SqlParameter[]> submit_SqlAndParms = BuildSubmitSQL(submitModel);
            KeyValuePair<string, SqlParameter[]> log_SqlAndParms = BuildLogSQL(logModel);
            Dictionary<string,SqlParameter[]> dict = new Dictionary<string,SqlParameter[]>();
            dict.Add(submit_SqlAndParms.Key ,submit_SqlAndParms.Value);
            dict.Add(log_SqlAndParms.Key ,log_SqlAndParms.Value);
            DbHelperSQL.ExecuteSqlTran(dict);
        }
        private KeyValuePair<string, SqlParameter[]> BuildLogSQL(OrderLogModel logModel)
        {
            string sql = @" INSERT INTO [dbo].[biz_OrderLog]([OrderID],[Dtm],[UserID],[UserName],[OpType],[CurState],[NextState],[NextUser],[Note])
            VALUES(@OrderID,@Dtm,@UserID,@UserName,@OpType,@CurState,@NextState,@NextUser,@Note);";
            SqlParameter[] parms = new SqlParameter[9];
            (parms[0] = new SqlParameter("@OrderID", SqlDbType.Int)).Value = logModel.OrderID;
            (parms[1] = new SqlParameter("@Dtm", SqlDbType.SmallDateTime)).Value = logModel.Dtm;
            (parms[2] = new SqlParameter("@UserID", SqlDbType.Int)).Value = logModel.UserID;
            (parms[3] = new SqlParameter("@UserName", SqlDbType.NVarChar,30)).Value = logModel.UserName;
            (parms[4] = new SqlParameter("@OpType", SqlDbType.TinyInt)).Value = logModel.OpType.Value;
            (parms[5] = new SqlParameter("@CurState", SqlDbType.Int)).Value = logModel.CurState.Value;
            (parms[6] = new SqlParameter("@NextState", SqlDbType.Int)).Value = logModel.NextState.Value;
            (parms[7] = new SqlParameter("@NextUser", SqlDbType.Int)).Value = logModel.NextUser.Value;
            (parms[8] = new SqlParameter("@Note", SqlDbType.NVarChar, 500)).Value = logModel.Note;
            return new KeyValuePair<string, SqlParameter[]>(sql, parms);
        }
        private KeyValuePair<string,SqlParameter[]> BuildSubmitSQL(OrderSubmitModel submitModel)
        {
            string sql = "";
            SqlParameter[] parms = new SqlParameter[4];
            string updStr = "";
            if (submitModel.BackState.HasValue)//退回
            {
                (parms[0] = new SqlParameter("@BackState", SqlDbType.Int)).Value = submitModel.BackState.Value;
                updStr = "BackState=@BackState";
            }
            else
            {
                (parms[0] = new SqlParameter("@PreSaleState", SqlDbType.Int)).Value = submitModel.SubmitSaleStateLog;
                updStr = "PreSaleState=@PreSaleState";
            }
            (parms[1] = new SqlParameter("@CurOpUserID", SqlDbType.Int)).Value = submitModel.NextOpUserID;
            (parms[2] = new SqlParameter("@CurOpUserName", SqlDbType.NVarChar,20)).Value = submitModel.NextOpUserName;
            (parms[3] = new SqlParameter("@ID", SqlDbType.Int)).Value = submitModel.OrderID;
            sql = "update dbo.biz_Order set " + updStr + ",CurOpUserID=@CurOpUserID,CurOpUserName=@CurOpUserName where ID=@ID";
            return new KeyValuePair<string, SqlParameter[]>(sql, parms);
        }

        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from biz_Order");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Slja.Model.OrderModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into biz_Order(");
            strSql.Append("PrjID,PrjName,ManagerUser,PlanStartDate,PlanEndDate,ShopID,ClientID,Region,City,ProductType,SaleType,AgentClientID,PlanMaker,SaleMan,AgentSaleMan,CreateTime,Creator,PreSaleState,CurOpUserID,CurOpUserName)");
            strSql.Append(" values (");
            strSql.Append("@PrjID,@PrjName,@ManagerUser,@PlanStartDate,@PlanEndDate,@ShopID,@ClientID,@Region,@City,@ProductType,@SaleType,@AgentClientID,@PlanMaker,@SaleMan,@AgentSaleMan,@CreateTime,@Creator,@PreSaleState,@CurOpUserID,@CurOpUserName)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@PrjID", SqlDbType.NVarChar,20),
					new SqlParameter("@PrjName", SqlDbType.NVarChar,50),
					new SqlParameter("@ManagerUser", SqlDbType.Int,4),
					new SqlParameter("@PlanStartDate", SqlDbType.SmallDateTime),
					new SqlParameter("@PlanEndDate", SqlDbType.SmallDateTime),
					new SqlParameter("@ShopID", SqlDbType.Int,4),
					new SqlParameter("@ClientID", SqlDbType.Int,4),
					new SqlParameter("@Region", SqlDbType.NVarChar,20),
					new SqlParameter("@City", SqlDbType.Int,4),
					new SqlParameter("@ProductType", SqlDbType.NVarChar,10),
					new SqlParameter("@SaleType", SqlDbType.TinyInt,1),
					new SqlParameter("@AgentClientID", SqlDbType.Int,4),
					new SqlParameter("@PlanMaker", SqlDbType.Int,4),
					new SqlParameter("@SaleMan", SqlDbType.Int,4),
					new SqlParameter("@AgentSaleMan", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Creator", SqlDbType.Int,4),
                    new SqlParameter("@PreSaleState", SqlDbType.Int,4),
                    new SqlParameter("@CurOpUserID", SqlDbType.Int,4),
                    new SqlParameter("@CurOpUserName", SqlDbType.NVarChar,20)
                                        };
            parameters[0].Value = model.PrjID;
            parameters[1].Value = model.PrjName;
            parameters[2].Value = model.ManagerUser;
            parameters[3].Value = model.PlanStartDate;
            parameters[4].Value = model.PlanEndDate;
            parameters[5].Value = model.ShopID;
            parameters[6].Value = model.ClientID;
            parameters[7].Value = model.Region;
            parameters[8].Value = model.City;
            parameters[9].Value = model.ProductType;
            parameters[10].Value = model.SaleType;
            parameters[11].Value = model.AgentClientID;
            parameters[12].Value = model.PlanMaker;
            parameters[13].Value = model.SaleMan;
            parameters[14].Value = model.AgentSaleMan;
            parameters[15].Value = model.CreateTime;
            parameters[16].Value = model.Creator;
            parameters[17].Value = (int)model.Enum_SaleState;
            parameters[18].Value = model.ManagerUser;
            parameters[19].Value = model.ManagerUserName;


            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Slja.Model.OrderModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update biz_Order set ");
            strSql.Append("PrjName=@PrjName,");
            strSql.Append("ManagerUser=@ManagerUser,");
            strSql.Append("PlanStartDate=@PlanStartDate,");
            strSql.Append("PlanEndDate=@PlanEndDate,");
            strSql.Append("ShopID=@ShopID,");
            strSql.Append("ClientID=@ClientID,");
            strSql.Append("Region=@Region,");
            strSql.Append("City=@City,");
            strSql.Append("ProductType=@ProductType,");
            strSql.Append("SaleType=@SaleType,");
            strSql.Append("AgentClientID=@AgentClientID,");
            strSql.Append("PlanMaker=@PlanMaker,");
            strSql.Append("SaleMan=@SaleMan,");
            strSql.Append("AgentSaleMan=@AgentSaleMan,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("Creator=@Creator");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@PrjName", SqlDbType.NVarChar,50),
					new SqlParameter("@ManagerUser", SqlDbType.Int,4),
					new SqlParameter("@PlanStartDate", SqlDbType.SmallDateTime),
					new SqlParameter("@PlanEndDate", SqlDbType.SmallDateTime),
					new SqlParameter("@ShopID", SqlDbType.Int,4),
					new SqlParameter("@ClientID", SqlDbType.Int,4),
					new SqlParameter("@Region", SqlDbType.NVarChar,20),
					new SqlParameter("@City", SqlDbType.Int,4),
					new SqlParameter("@ProductType", SqlDbType.NVarChar,10),
					new SqlParameter("@SaleType", SqlDbType.TinyInt,1),
					new SqlParameter("@AgentClientID", SqlDbType.Int,4),
					new SqlParameter("@PlanMaker", SqlDbType.Int,4),
					new SqlParameter("@SaleMan", SqlDbType.Int,4),
					new SqlParameter("@AgentSaleMan", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Creator", SqlDbType.Int,4),
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@PrjID", SqlDbType.NVarChar,20)};
            parameters[0].Value = model.PrjName;
            parameters[1].Value = model.ManagerUser;
            parameters[2].Value = model.PlanStartDate;
            parameters[3].Value = model.PlanEndDate;
            parameters[4].Value = model.ShopID;
            parameters[5].Value = model.ClientID;
            parameters[6].Value = model.Region;
            parameters[7].Value = model.City;
            parameters[8].Value = model.ProductType;
            parameters[9].Value = model.SaleType;
            parameters[10].Value = model.AgentClientID;
            parameters[11].Value = model.PlanMaker;
            parameters[12].Value = model.SaleMan;
            parameters[13].Value = model.AgentSaleMan;
            parameters[14].Value = model.CreateTime;
            parameters[15].Value = model.Creator;
            parameters[16].Value = model.ID;
            parameters[17].Value = model.PrjID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool UpdateDesignMemo(Slja.Model.OrderModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update biz_Order set ");
            strSql.Append("DesignMemo=@DesignMemo ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@DesignMemo", SqlDbType.NVarChar,-1),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = model.DesignMemo;
            parameters[1].Value = model.ID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool UpdateSingelField(int OrderID,KeyValuePair<OrderField,string> UpdateFieldVal)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update biz_Order set ");
            strSql.Append(UpdateFieldVal.Key.ToString()+"=@"+UpdateFieldVal.Key.ToString());
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@"+UpdateFieldVal.Key.ToString(), SqlDbType.NVarChar),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = UpdateFieldVal.Value;
            parameters[1].Value = OrderID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from biz_Order ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from biz_Order ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Slja.Model.OrderModel GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,PrjID,PrjName,ManagerUser,PlanStartDate,PlanEndDate,ShopID,ClientID,Region,City,ProductType,SaleType,AgentClientID,PlanMaker,SaleMan,AgentSaleMan,CreateTime,Creator from biz_Order ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = ID;

            Slja.Model.OrderModel model = new Slja.Model.OrderModel();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                AssignModel(ds.Tables[0].Rows[0], model);
                return model;
            }
            else
            {
                return null;
            }
        }
        public Slja.Model.OrderExtModel GetModelExt(int ID)
        {
            SqlParameter[] parms ={ new SqlParameter("@ID",SqlDbType.Int)};
            parms[0].Value= ID;
            string strSQL = BuildOrderListSQL("a.ID=@ID");
            Slja.Model.OrderExtModel modelext = new Slja.Model.OrderExtModel();
            DataSet ds = DbHelperSQL.Query(strSQL, parms);
            if (ds.Tables[0].Rows.Count > 0)
            {
                AssignExtModel(ds.Tables[0].Rows[0], modelext);
                return modelext;
            }
            else
            {
                return null;
            }
        }
        public void AssignExtModel(DataRow dr, OrderExtModel model)
        {
            AssignModel(dr, model);
            if (dr["cityname"] != null && dr["cityname"].ToString() != "")
            {
                model.CityName = dr["cityname"].ToString();
            }
            if (dr["ShopName"] != null && dr["ShopName"].ToString() != "")
            {
                model.ShopName = dr["ShopName"].ToString();
            }
            if (dr["shoplinkman"] != null && dr["shoplinkman"].ToString() != "")
            {
                model.ShopLinkMan = dr["shoplinkman"].ToString();
            }
            if (dr["shoplinkmanTel"] != null && dr["shoplinkmanTel"].ToString() != "")
            {
                model.ShopLinkManTel = dr["shoplinkmanTel"].ToString();
            }
            if (dr["shoppromotor"] != null && dr["shoppromotor"].ToString() != "")
            {
                model.ShopPromotor = dr["shoppromotor"].ToString();
            }
            if (dr["TLCode"] != null && dr["TLCode"].ToString() != "")
            {
                model.TLCode = dr["TLCode"].ToString();
            }
            if (dr["Innercode"] != null && dr["Innercode"].ToString() != "")
            {
                model.ShopInnerCode = dr["Innercode"].ToString();
            }
            if (dr["address"] != null && dr["address"].ToString() != "")
            {
                model.Address = dr["address"].ToString();
            }
            if (dr["ShopLev"] != null && dr["ShopLev"].ToString() != "")
            {
                model.ShopLev = dr["ShopLev"].ToString();
            }
            if (dr["SaleVolume"] != null && dr["SaleVolume"].ToString() != "")
            {
                model.SaleVolume = dr["SaleVolume"].ToString();
            }
            if (dr["Square"] != null && dr["Square"].ToString() != "")
            {
                model.ShopSquare = dr["Square"].ToString();
            }
            if (dr["ClientName"] != null && dr["ClientName"].ToString() != "")
            {
                model.ClientName = dr["ClientName"].ToString();
            }
            if (dr["PlanMakerName"] != null && dr["PlanMakerName"].ToString() != "")
            {
                model.PlanMakerName = dr["PlanMakerName"].ToString();
            }
            if (dr["PlanmakerTel"] != null && dr["PlanmakerTel"].ToString() != "")
            {
                model.PlanMakerTel = dr["PlanmakerTel"].ToString();
            }
            if (dr["SalemanName"] != null && dr["SalemanName"].ToString() != "")
            {
                model.SalemanName = dr["SalemanName"].ToString();
            }
            if (dr["SalemanNameTel"] != null && dr["SalemanNameTel"].ToString() != "")
            {
                model.SalemanTel = dr["SalemanNameTel"].ToString();
            }
            if (dr["AgentClientName"] != null && dr["AgentClientName"].ToString() != "")
            {
                model.AgentClientName = dr["AgentClientName"].ToString();
            }
            if (dr["AgentClientlinkman"] != null && dr["AgentClientlinkman"].ToString() != "")
            {
                model.AgentClientLinkman = dr["AgentClientlinkman"].ToString();
            }
            if (dr["agentclientlinkTel"] != null && dr["agentclientlinkTel"].ToString() != "")
            {
                model.AgentClientLinkTel = dr["agentclientlinkTel"].ToString();
            }
            if (dr["Presalestate"] != null && dr["Presalestate"].ToString() != "")
            {
                model.SaleStateLog = int.Parse(dr["Presalestate"].ToString());
            }
            if (dr["BackState"] != null && dr["BackState"].ToString() != "")
            {
                model.BackState = int.Parse(dr["BackState"].ToString());
            }
            if (dr["ManagerName"] != null && dr["ManagerName"].ToString() != "")
            {
                model.ManagerUserName = dr["ManagerName"].ToString();
            }
            /* 以下给几个状态赋值 */
            SaleState state = GetCurrentSaleState(model.SaleStateLog);
            model.Enum_SaleState = state;
            model.Enum_BackState = (SaleState)model.BackState;
            model.StatusDescr = state.ToDescription();
          
        }
        private SaleState GetCurrentSaleState(int nSaleState)
        {
            return (SaleState)((nSaleState + 1) / 2);
        }
        public void AssignModel(DataRow dr, OrderModel model)
        {
            if (dr["ID"] != null && dr["ID"].ToString() != "")
            {
                model.ID = int.Parse(dr["ID"].ToString());
            }
            if (dr["PrjID"] != null && dr["PrjID"].ToString() != "")
            {
                model.PrjID = dr["PrjID"].ToString();
            }
            if (dr["PrjName"] != null && dr["PrjName"].ToString() != "")
            {
                model.PrjName = dr["PrjName"].ToString();
            }
            if (dr["ManagerUser"] != null && dr["ManagerUser"].ToString() != "")
            {
                model.ManagerUser = int.Parse(dr["ManagerUser"].ToString());
            }
            if (dr["PlanStartDate"] != null && dr["PlanStartDate"].ToString() != "")
            {
                model.PlanStartDate = DateTime.Parse(dr["PlanStartDate"].ToString());
            }
            if (dr["PlanEndDate"] != null && dr["PlanEndDate"].ToString() != "")
            {
                model.PlanEndDate = DateTime.Parse(dr["PlanEndDate"].ToString());
            }
            if (dr["ShopID"] != null && dr["ShopID"].ToString() != "")
            {
                model.ShopID = int.Parse(dr["ShopID"].ToString());
            }
            if (dr["ClientID"] != null && dr["ClientID"].ToString() != "")
            {
                model.ClientID = int.Parse(dr["ClientID"].ToString());
            }
            if (dr["Region"] != null && dr["Region"].ToString() != "")
            {
                model.Region = dr["Region"].ToString();
            }
            if (dr["City"] != null && dr["City"].ToString() != "")
            {
                model.City = int.Parse(dr["City"].ToString());
            }
            if (dr["ProductType"] != null && dr["ProductType"].ToString() != "")
            {
                model.ProductType = dr["ProductType"].ToString();
            }
            if (dr["SaleType"] != null && dr["SaleType"].ToString() != "")
            {
                model.SaleType = int.Parse(dr["SaleType"].ToString());
            }
            if (dr["AgentClientID"] != null && dr["AgentClientID"].ToString() != "")
            {
                model.AgentClientID = int.Parse(dr["AgentClientID"].ToString());
            }
            if (dr["PlanMaker"] != null && dr["PlanMaker"].ToString() != "")
            {
                model.PlanMaker = int.Parse(dr["PlanMaker"].ToString());
            }
            if (dr["SaleMan"] != null && dr["SaleMan"].ToString() != "")
            {
                model.SaleMan = int.Parse(dr["SaleMan"].ToString());
            }
            if (dr["AgentSaleMan"] != null && dr["AgentSaleMan"].ToString() != "")
            {
                model.AgentSaleMan = dr["AgentSaleMan"].ToString();
            }
            if (dr["CreateTime"] != null && dr["CreateTime"].ToString() != "")
            {
                model.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
            }
            if (dr["Creator"] != null && dr["Creator"].ToString() != "")
            {
                model.Creator = int.Parse(dr["Creator"].ToString());
            }
            if (dr.Table.Columns.Contains("curOpUserID") && dr["curOpUserID"] != DBNull.Value && dr["curOpUserID"].ToString() != "")
            {
                model.CurOpUserID = int.Parse(dr["curOpUserID"].ToString());
            }
            if (dr.Table.Columns.Contains("curOpUserName") && dr["curOpUserName"] != DBNull.Value && dr["curOpUserName"].ToString() != "")
            {
                model.CurOpUserName = dr["curOpUserName"].ToString();
            }
            if (dr.Table.Columns.Contains("ClientMemo") && dr["ClientMemo"] != DBNull.Value)
            {
                model.ClientMemo = dr["ClientMemo"].ToString();
            }
            if (dr.Table.Columns.Contains("ManageMemo") && dr["ManageMemo"] != DBNull.Value)
            {
                model.ManageMemo = dr["ManageMemo"].ToString();
            }
            if (dr.Table.Columns.Contains("DesignMemo") && dr["DesignMemo"] != DBNull.Value)
            {
                model.DesignMemo = dr["DesignMemo"].ToString();
            }
            if (dr.Table.Columns.Contains("PriceMemo") && dr["PriceMemo"] != DBNull.Value)
            {
                model.PriceMemo = dr["PriceMemo"].ToString();
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PrjID,PrjName,ManagerUser,PlanStartDate,PlanEndDate,ShopID,ClientID,Region,City,ProductType,SaleType,AgentClientID,PlanMaker,SaleMan,AgentSaleMan,CreateTime,Creator ");
            strSql.Append(" FROM biz_Order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetOrderExtList(string strWhere)
        {
            string sql = BuildOrderListSQL(strWhere);

            return DbHelperSQL.Query(sql.ToString());
        }
        private string BuildOrderListSQL(string strWhere)
        {
            string strSql = @"
            select a.ID,a.PrjID,a.PrjName,a.ManagerUser,a.PlanStartDate,a.PlanEndDate,a.ShopID,a.ClientID,a.Region,a.ProductType,a.SaleType,
				a.PlanMaker,a.SaleMan,a.AgentSaleMan,a.CreateTime,a.Creator,a.PreSaleState,a.BackState,a.CurOpUserID,a.CurOpUserName
                ,a.ClientMemo,a.ManageMemo,a.DesignMemo,a.PriceMemo
		       ,b.ShopName,b.City,b.Address,b.AgentClientID,b.TLCode,b.InnerCode,b.ShopLev,b.SaleVolume,b.[Square],b.ShopPromotor,b.ShopLinkman,b.ShopLinkmanTel
		       ,c.f_title as cityname,cl.ClientName,ur.U_CName as ManagerName
                ,l1.FullName as PlanMakerName,l1.MobileTel+','+l1.JobTel as PlanmakerTel
                ,l2.FullName as SalemanName,l2.MobileTel+','+l1.JobTel as SalemanNameTel
                ,ag.ClientName as AgentClientName,l3.FullName as agentclientlinkman,l3.MobileTel+','+l3.JobTel as agentclientlinkTel
                from dbo.biz_Order a inner join dbo.biz_Shop b on a.ShopID=b.ShopID
                left join dbo.biz_City c on a.city=c.fid
                left join dbo.biz_LinkMan l1 on a.PlanMaker=l1.ID
                left join dbo.biz_LinkMan l2 on a.SaleMan=l2.ID
			    left join dbo.biz_Client cl on a.ClientID=cl.ClientID
                left join dbo.biz_Client ag on a.AgentClientID=ag.ClientID
                left join dbo.biz_LinkMan l3 on l3.ID=ag.LinkMan
			    left join dbo.sys_User ur on a.ManagerUser=ur.UserID
            ";
            if (strWhere != "")
            {
                strSql += " where " + strWhere;
            }
            return strSql;
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,PrjID,PrjName,ManagerUser,PlanStartDate,PlanEndDate,ShopID,ClientID,Region,City,ProductType,SaleType,AgentClientID,PlanMaker,SaleMan,AgentSaleMan,CreateTime,Creator ");
            strSql.Append(" FROM biz_Order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM biz_Order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }       
        public DataSet GetListByPage(string strWhere, string orderby, int pageSize, int curPage, out int recordCount)
        { 
            string sql = BuildOrderListSQL(strWhere);
            DataSet ds=DALUti.GetPageData(sql, orderby, pageSize, curPage,out recordCount);
            return ds;
        }
        #endregion  Method
    }
}

